/*
 *	This file is part of Lounge-Cast.
 *
 *  Author:  	Markus Moormann
 *	E-mail:  	mmoorman@uni-osnabrueck.de
 *	Copyright:	University of Osnabrueck (Zentrum Virtuos)
 * 	Website:	www.uni-osnabrueck.de
 *	Date:		16.02.2011
 *
 *	Lounge-Cast is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *	Lounge-Cast is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with Lounge-Cast.  If not, see <http://www.gnu.org/licenses/>.
 */
/**
 * 
 */
package backend.service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import backend.dbConnection.ConnectionHelper;
import backend.vo.GroupVO;
import backend.vo.PlaylistVO;
import backend.vo.UserVO;

/**
 * class for user service
 * 
 * @author Markus Moormann
 */
public class UserService {

	
	/**
	 * a logger to log errors, debug information, etc.
	 */
	private static Logger LOG = Logger.getLogger(UserService.class);
	/**
	 * the connection to database
	 */
	//private Connection connection;
	/**
	 * the statement so send to database
	 */
	//private Statement statement;
	
	/**
	 * get all users from database
	 * 
	 * @return a <code>List&lt;List&lt;UserVO&gt;&gt;</code> grouping the users by its user_group
	 */
	public List<List<UserVO>> getUser() {
		List<List<UserVO>> result = new LinkedList<List<UserVO>>();
		List<UserVO> user = new LinkedList<UserVO>();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectUser = "SELECT " +
									"u.user_id," +
									"u.user_name," +
									"u.user_email," +
									"u.is_ldap," +
									"u.user_password," +
									"g.group_id," +
									"g.description " +
								"FROM " +
									"user u," +
									"user_group g " +
								"WHERE " +
									"u.group_id != 0 AND " +
									"u.group_id = g.group_id " +
								"ORDER BY " +
									"g.group_id";
			
			ResultSet rs = statement.executeQuery(selectUser);
			int lastGroupId = 0;
			while(rs.next()) {
				if(lastGroupId != rs.getInt("group_id")) {
					if(user.size() > 0) {
						result.add(user);
						user = new LinkedList<UserVO>();
					}
					lastGroupId = rs.getInt("group_id");
				}
				UserVO tmpUser = new UserVO(rs.getInt("user_id"), rs.getString("user_name"), 
											new GroupVO(rs.getInt("group_id"), rs.getString("description")));
				tmpUser.user_email = rs.getString("user_email");
				tmpUser.is_ldap = rs.getBoolean("is_ldap");
				tmpUser.user_password = rs.getString("user_password");
				tmpUser.playlists = getUserPlaylists(tmpUser.user_id);
				user.add(tmpUser);
			}
			if(user.size() > 0) {
				result.add(user);
			}
			
		} catch (SQLException e) {
			LOG.error(e);
		} finally {
			ConnectionHelper.close(statement);
			ConnectionHelper.close(connection);
		}
		return result;
	}
	
	/**
	 * get all playlists this user can edit
	 * 
	 * @param user_id the id of the user
	 * @return a list containing all playlists the user can edit 
	 */
	private List<PlaylistVO> getUserPlaylists(int user_id) {
		List<PlaylistVO> result = new LinkedList<PlaylistVO>();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String getUserPlaylists = 	"SELECT " +
											"mp.playlist_id," +
											"inheritance_level," +
											"inheritance_from, " +
											"description  " +
										"FROM " +
											"media_playlist_user mpu, " +
											"media_playlist mp " +
										"WHERE " +
											"user_id = " + user_id + " AND " +
											"mpu.playlist_id = mp.playlist_id";
			ResultSet rs = statement.executeQuery(getUserPlaylists);
			while(rs.next()) {
				PlaylistVO playlist = new PlaylistVO();
				playlist.playlist_id = rs.getInt("playlist_id");
				playlist.inheritance_level = rs.getInt("inheritance_level");
				playlist.inheritance_from = null;
				playlist.description = rs.getString("description");
				result.add(playlist);
			}
		} catch (SQLException e) {
			LOG.error(e);
		} finally
		{
			ConnectionHelper.close(statement);
			ConnectionHelper.close(connection);
		}
		return result;
	}
	

	/**
	 * get all user groups
	 * 
	 * @return a list containing all user groups
	 */
	public List<GroupVO> getUserGroups() {
		List<GroupVO> result = new LinkedList<GroupVO>();
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String selectGroups = 	"SELECT " +
										"group_id," +
										"description " +
									"FROM " +
										"user_group " +
									"WHERE " +
										"group_id > 0";
			ResultSet rs = statement.executeQuery(selectGroups);
			while(rs.next()) {
				result.add(new GroupVO(rs.getInt("group_id"), rs.getString("description")));
			}
		} catch (SQLException e) {
			LOG.error(e);
		} finally {
			ConnectionHelper.close(statement);
			ConnectionHelper.close(connection);
		}
		return result;
	}
	
	/**
	 * add a playlist to users competency
	 * 
	 * @param user the user to to add the playlist to
	 * @param playlist the playlist to add
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean addPlaylist(UserVO user, PlaylistVO playlist) {
		
		return false;
	}
	
	/**
	 * remove a playlist from users competency
	 * 
	 * @param user the user from whom to remove the playlist
	 * @param playlist the playlist to remove
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean removePlaylist(UserVO user, PlaylistVO playlist) {
		
		return false;
	}
	
	/**
	 * update a user in the database
	 * 
	 * @param user a <code>UserVO</code> containing all information
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean updateUser(UserVO user) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String updateUser = 	"UPDATE " +
										"user " +
									"SET " +
										"user_name 		= '" + user.user_name 		+ "', " +
										"user_email 	= '" + user.user_email 		+ "'," +
										"user_password 	= '" + user.user_password	+ "'," +
										"is_ldap		= '" + user.is_ldap 		+ "'," +
										"group_id 		= "  + user.user_group.group_id + " " +
									"WHERE " +
										"user_id = " + user.user_id;
			statement.execute(updateUser);
			String deletePlaylists = "DELETE FROM " +
									 	"media_playlist_user " +
									 "WHERE " +
									 	"user_id = " + user.user_id;
			statement.execute(deletePlaylists);
			for(int i = 0; i < user.playlists.size(); i++) {
				String addPlaylists = 	"INSERT INTO " +
											"media_playlist_user " +
												"(" +
													"playlist_id," +
													"user_id" +
												")VALUES" +
												"(" +
													user.playlists.get(i).playlist_id + "," +
													user.user_id +
												")";
				statement.execute(addPlaylists);
			}
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		} finally {
			ConnectionHelper.close(statement);
			ConnectionHelper.close(connection);
		}
		return true;
	}
	
	/**
	 * insert a user in database
	 * 
	 * @param user a <code>UserVO</code> containing all information
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean saveUser(UserVO user) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String insertUser = 	"INSERT INTO " +
										"user " +
									"SET " +
										"user_name 		= '" + user.user_name + "', " +
										"user_email 	= '" + user.user_email + "'," +
										"user_password 	= '" + user.user_password	+ "'," +
										"is_ldap		= '" + user.is_ldap 		+ "'," +
										"group_id 		= " + user.user_group.group_id;
			statement.execute(insertUser);
			ResultSet rs = statement.getGeneratedKeys();
			if(rs.next())
			{
				user.user_id = rs.getInt(1);
				for(int i = 0; i < user.playlists.size(); i++) {
					String addPlaylists = 	"INSERT INTO " +
												"media_playlist_user " +
													"(" +
														"playlist_id," +
														"user_id" +
													")VALUES" +
													"(" +
														user.playlists.get(i).playlist_id + "," +
														user.user_id +
													")";
					statement.execute(addPlaylists);
				}
			}
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		} finally {
			ConnectionHelper.close(statement);
			ConnectionHelper.close(connection);
		}
		return true;
	}

	/**
	 * delete a user from database
	 * 
	 * @param user a <code>UserVO</code> containing all information
	 * @return <code>true</code> on success, <code>false</code> else
	 */
	public boolean deleteUser(UserVO user) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = ConnectionHelper.getConnection();
			statement = connection.createStatement();
			
			String insertUser = "DELETE FROM " +
									"user " +
								"WHERE " +
									"user_id = " + user.user_id;
			statement.execute(insertUser);
		} catch (SQLException e) {
			LOG.error(e);
			return false;
		} finally {
			ConnectionHelper.close(statement);
			ConnectionHelper.close(connection);
		}
		return true;
	}
}
